package cn.iocoder.yudao.module.system.dal.mysql.collegeenrollmentplan;

import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
import cn.iocoder.yudao.module.system.dal.dataobject.collegeenrollmentplan.CollegeEnrollmentPlanDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * 历年高校招生计划数据 Mapper
 *
 * @author 芋道源码
 */
@Mapper
public interface CollegeEnrollmentPlanMapper extends BaseMapperX<CollegeEnrollmentPlanDO> {

    Logger log = LoggerFactory.getLogger(CollegeEnrollmentPlanMapper.class);

    /**
     * 根据省份和年份查询招生计划数据
     * @param provinceName 省份名称
     * @param year 年份
     * @return 招生计划数据列表
     */
    default List<CollegeEnrollmentPlanDO> selectListByProvinceAndYearAndType(String provinceName, Integer year, String type) {
        return selectList(new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>()
                .eq(CollegeEnrollmentPlanDO::getProvinceName, provinceName)
                .eq(CollegeEnrollmentPlanDO::getYear, year).eq(CollegeEnrollmentPlanDO::getType,type));
    }

    /**
     * 根据学校名称和专业名称查询招生计划数据
     * @param schoolName 学校名称
     * @param majorName 专业名称
     * @param provinceName 省份名称
     * @param year 年份
     * @return 招生计划数据列表
     */
    default List<CollegeEnrollmentPlanDO> selectListBySchoolAndMajor(String schoolName, String majorName,
                                                                    String provinceName, Integer year) {
        return selectList(new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>()
                .likeIfPresent(CollegeEnrollmentPlanDO::getSchoolName, schoolName)
                .likeIfPresent(CollegeEnrollmentPlanDO::getCollegeMajorName, majorName)
                .eqIfPresent(CollegeEnrollmentPlanDO::getProvinceName, provinceName)
                .eqIfPresent(CollegeEnrollmentPlanDO::getYear, year));
    }

    /**
     * 批量查询招生计划数据（完整数据）
     * @param schoolNames 学校名称列表
     * @param majorNames 专业名称列表
     * @param provinceName 省份名称
     * @param year 年份
     * @return 招生计划完整数据列表
     */
    default List<CollegeEnrollmentPlanDO> selectExistingEnrollmentPlans(List<String> schoolNames,
                                                                       List<String> majorNames,
                                                                       String provinceName, Integer year) {
        if (schoolNames.isEmpty() || majorNames.isEmpty()) {
            return new ArrayList<>();
        }

        return selectList(new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>()
                .in(CollegeEnrollmentPlanDO::getSchoolName, schoolNames)
                .in(CollegeEnrollmentPlanDO::getCollegeMajorName, majorNames)
                .eqIfPresent(CollegeEnrollmentPlanDO::getProvinceName, provinceName)
                .eqIfPresent(CollegeEnrollmentPlanDO::getYear, year));
    }

    /**
     * 批量查询招生计划数据（支持括号内容匹配）
     * 使用自定义SQL来处理专业名称中的括号内容差异，使用INSTR函数优化性能
     * 支持中文括号（）和英文括号()两种格式
     * 支持两种匹配方式：
     * 1. 匹配括号前的专业名称（如：计算机类 匹配 计算机类(计算机科学与技术、软件工程) 或 计算机类（计算机科学与技术、软件工程））
     * 2. 匹配括号内的专业名称（如：软件工程 匹配 计算机类(计算机科学与技术、软件工程) 或 计算机类（计算机科学与技术、软件工程））
     *    - 使用INSTR函数查找 '(专业名' 和 '（专业名' 模式
     *    - 使用INSTR函数查找 '、专业名' 模式（处理顿号分隔的情况）
     * @param schoolNames 学校名称列表
     * @param majorNames 专业名称列表（去除括号后的）
     * @param provinceName 省份名称
     * @param year 年份
     * @return 招生计划完整数据列表
     */
    @Select("<script>" +
            "SELECT * FROM system_college_enrollment_plan " +
            "WHERE school_name IN " +
            "<foreach collection='schoolNames' item='schoolName' open='(' separator=',' close=')'>" +
            "#{schoolName}" +
            "</foreach>" +
            " AND (" +
            "   (SUBSTRING_INDEX(college_major_name, '(', 1) IN " +
            "   <foreach collection='majorNames' item='majorName' open='(' separator=',' close=')'>" +
            "   #{majorName}" +
            "   </foreach>" +
            "   OR SUBSTRING_INDEX(college_major_name, '（', 1) IN " +
            "   <foreach collection='majorNames' item='majorName' open='(' separator=',' close=')'>" +
            "   #{majorName}" +
            "   </foreach>)" +
            "   OR " +
            "   <foreach collection='majorNames' item='majorName' open='' separator=' OR ' close=''>" +
            "   (INSTR(college_major_name, CONCAT('(', #{majorName})) > 0 OR " +
            "    INSTR(college_major_name, CONCAT('（', #{majorName})) > 0 OR " +
            "    INSTR(college_major_name, CONCAT('、', #{majorName})) > 0)" +
            "   </foreach>" +
            " )" +
            "<if test='provinceName != null and provinceName != \"\"'>" +
            " AND province_name = #{provinceName}" +
            "</if>" +
            "<if test='year != null'>" +
            " AND year = #{year}" +
            "</if>" +
            "</script>")
    List<CollegeEnrollmentPlanDO> selectExistingEnrollmentPlansWithBracketHandling(
            @Param("schoolNames") List<String> schoolNames,
            @Param("majorNames") List<String> majorNames,
            @Param("provinceName") String provinceName,
            @Param("year") Integer year);

    /**
     * 批量查询招生计划数据（支持括号内容匹配、专业组智能匹配和typeName过滤）
     * 在原有括号匹配基础上增加专业组智能匹配和typeName过滤，提高查询效率
     * @param schoolNames 学校名称列表
     * @param majorNames 专业名称列表（去除括号后的）
     * @param provinceName 省份名称
     * @param year 年份
     * @param typeName 类型名称（如物理类、历史类等）
     * @param subjectGroup 专业组信息（用于智能匹配）
     * @return 招生计划完整数据列表
     */
    @Select("<script>" +
            "SELECT * FROM system_college_enrollment_plan " +
            "WHERE school_name IN " +
            "<foreach collection='schoolNames' item='schoolName' open='(' separator=',' close=')'>" +
            "#{schoolName}" +
            "</foreach>" +
            " AND (" +
            "   (SUBSTRING_INDEX(college_major_name, '(', 1) IN " +
            "   <foreach collection='majorNames' item='majorName' open='(' separator=',' close=')'>" +
            "   #{majorName}" +
            "   </foreach>" +
            "   OR SUBSTRING_INDEX(college_major_name, '（', 1) IN " +
            "   <foreach collection='majorNames' item='majorName' open='(' separator=',' close=')'>" +
            "   #{majorName}" +
            "   </foreach>)" +
            "   OR " +
            "   <foreach collection='majorNames' item='majorName' open='' separator=' OR ' close=''>" +
            "   (INSTR(college_major_name, CONCAT('(', #{majorName})) > 0 OR " +
            "    INSTR(college_major_name, CONCAT('（', #{majorName})) > 0 OR " +
            "    INSTR(college_major_name, CONCAT('、', #{majorName})) > 0)" +
            "   </foreach>" +
            " )" +
            "<if test='provinceName != null and provinceName != \"\"'>" +
            " AND province_name = #{provinceName}" +
            "</if>" +
            "<if test='year != null'>" +
            " AND year = #{year}" +
            "</if>" +
            "<if test='typeName != null and typeName != \"\"'>" +
            " AND type = #{typeName}" +
            "</if>" +
            "<if test='subjectGroup != null and subjectGroup != \"\"'>" +
            " AND (" +
            "   special_group_name = #{subjectGroup} OR " +
            "   special_group_name LIKE CONCAT('%', #{subjectGroup}, '%') OR " +
            "   (LOCATE('(', special_group_name) > 0 AND LOCATE(')', special_group_name) > 0 AND " +
            "    SUBSTRING(special_group_name, LOCATE('(', special_group_name) + 1, " +
            "             LOCATE(')', special_group_name) - LOCATE('(', special_group_name) - 1) LIKE CONCAT('%', " +
            "             CASE WHEN #{subjectGroup} REGEXP '^[0-9]+$' THEN #{subjectGroup} " +
            "                  WHEN #{subjectGroup} REGEXP '^[A-Za-z][0-9]+$' THEN SUBSTRING(#{subjectGroup}, 2) " +
            "                  ELSE #{subjectGroup} END, '%')) OR " +
            "   (LOCATE('（', special_group_name) > 0 AND LOCATE('）', special_group_name) > 0 AND " +
            "    SUBSTRING(special_group_name, LOCATE('（', special_group_name) + 1, " +
            "             LOCATE('）', special_group_name) - LOCATE('（', special_group_name) - 1) LIKE CONCAT('%', " +
            "             CASE WHEN #{subjectGroup} REGEXP '^[0-9]+$' THEN #{subjectGroup} " +
            "                  WHEN #{subjectGroup} REGEXP '^[A-Za-z][0-9]+$' THEN SUBSTRING(#{subjectGroup}, 2) " +
            "                  ELSE #{subjectGroup} END, '%'))" +
            " )" +
            "</if>" +
            "</script>")
    List<CollegeEnrollmentPlanDO> selectExistingEnrollmentPlansWithBracketHandlingAndType(
            @Param("schoolNames") List<String> schoolNames,
            @Param("majorNames") List<String> majorNames,
            @Param("provinceName") String provinceName,
            @Param("year") Integer year,
            @Param("typeName") String typeName,
            @Param("subjectGroup") String subjectGroup);

    /**
     * 根据学校名称和专业名称查询招生计划数据（支持typeName过滤）
     * @param schoolName 学校名称
     * @param majorName 专业名称
     * @param provinceName 省份名称
     * @param year 年份
     * @param typeName 类型名称
     * @return 招生计划数据列表
     */
    default List<CollegeEnrollmentPlanDO> selectListBySchoolAndMajorWithType(String schoolName, String majorName,
                                                                            String provinceName, Integer year, String typeName) {
        return selectList(new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>()
                .likeIfPresent(CollegeEnrollmentPlanDO::getSchoolName, schoolName)
                .likeIfPresent(CollegeEnrollmentPlanDO::getCollegeMajorName, majorName)
                .eqIfPresent(CollegeEnrollmentPlanDO::getProvinceName, provinceName)
                .eqIfPresent(CollegeEnrollmentPlanDO::getYear, year)
                .eqIfPresent(CollegeEnrollmentPlanDO::getType, typeName));
    }

    /**
     * 检查指定的学校专业组合是否存在招生计划
     * @param provinceName 省份名称
     * @param schoolUuid 学校UUID
     * @param collegeMajorName 专业名称
     * @param year 年份
     * @param type 类型
     * @param batchName 批次名称
     * @return 是否存在
     */
    default boolean existsByUniqueKey(String provinceName, String schoolUuid, String collegeMajorName,
                                     Integer year, String type, String batchName) {
        LambdaQueryWrapperX<CollegeEnrollmentPlanDO> wrapper = new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>();

        // 使用 eq 而不是 eqIfPresent，确保 null 值也能正确匹配
        if (provinceName != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getProvinceName, provinceName);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getProvinceName);
        }

        if (schoolUuid != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getSchoolUuid, schoolUuid);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getSchoolUuid);
        }

        if (collegeMajorName != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getCollegeMajorName, collegeMajorName);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getCollegeMajorName);
        }

        if (year != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getYear, year);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getYear);
        }

        if (type != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getType, type);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getType);
        }

        if (batchName != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getBatchName, batchName);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getBatchName);
        }

        return selectCount(wrapper) > 0;
    }

    /**
     * 根据学校名称和专业名称精确查询招生计划（支持选科和类型过滤）
     * 优化版本：为单个专业查询招生计划，避免查询所有数据再匹配
     * 使用系统已有的专业名称匹配逻辑，支持括号差异处理
     * @param schoolName 学校名称
     * @param majorName 专业名称
     * @param provinceName 省份名称
     * @param year 年份
     * @param userSubjects 用户选科（可为null）
     * @param typeName 类型名称（可为null）
     * @return 符合条件的招生计划列表
     */
    default List<CollegeEnrollmentPlanDO> selectBySchoolAndMajorWithFilters(String schoolName, String majorName,
                                                                           String provinceName, Integer year,
                                                                           List<String> userSubjects, String typeName) {
        LambdaQueryWrapperX<CollegeEnrollmentPlanDO> wrapper = new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>()
                .eq(CollegeEnrollmentPlanDO::getSchoolName, schoolName)
                .eq(CollegeEnrollmentPlanDO::getProvinceName, provinceName)
                .eq(CollegeEnrollmentPlanDO::getYear, year);

        // 专业名称匹配 - 使用系统已有的三重匹配机制
        if (majorName != null && !majorName.trim().isEmpty()) {
            String cleanMajorName = removeBracketsFromMajorName(majorName);

            wrapper.and(w -> {
                // 1. 直接匹配
                w.eq(CollegeEnrollmentPlanDO::getCollegeMajorName, majorName);

                // 2. 模糊匹配
                w.or().like(CollegeEnrollmentPlanDO::getCollegeMajorName, majorName);

                // 3. 去括号匹配（如果去括号后的名称与原名称不同）
                if (!cleanMajorName.equals(majorName)) {
                    w.or().like(CollegeEnrollmentPlanDO::getCollegeMajorName, cleanMajorName);
                }

                // 4. 双向匹配：数据库专业名称去括号后匹配
                w.or().apply("SUBSTRING_INDEX(college_major_name, '(', 1) LIKE {0}", "%" + cleanMajorName + "%");
                w.or().apply("SUBSTRING_INDEX(college_major_name, '（', 1) LIKE {0}", "%" + cleanMajorName + "%");

                // 5. 括号内容匹配
                w.or().apply("INSTR(college_major_name, CONCAT('(', {0})) > 0", cleanMajorName);
                w.or().apply("INSTR(college_major_name, CONCAT('（', {0})) > 0", cleanMajorName);
                w.or().apply("INSTR(college_major_name, CONCAT('、', {0})) > 0", cleanMajorName);
                w.or().apply("INSTR(college_major_name, CONCAT('，', {0})) > 0", cleanMajorName);
            });
        }

        // 类型过滤
        if (typeName != null && !typeName.trim().isEmpty()) {
            wrapper.and(w -> w.eq(CollegeEnrollmentPlanDO::getType, typeName)
                    .or().like(CollegeEnrollmentPlanDO::getType, typeName));
        }

        List<CollegeEnrollmentPlanDO> plans = selectList(wrapper);

        // 选科过滤（在内存中进行，因为选科逻辑较复杂）
        if (userSubjects != null && !userSubjects.isEmpty()) {
            plans = plans.stream()
                    .filter(plan -> isSubjectRequirementMet(plan.getCourseSelectionRequirements(), userSubjects))
                    .collect(Collectors.toList());
        }

        return plans;
    }

    /**
     * 去除专业名称中的括号内容（支持中英文括号）
     * 使用系统已有的逻辑
     * @param majorName 专业名称
     * @return 清理后的专业名称
     */
    default String removeBracketsFromMajorName(String majorName) {
        if (majorName == null || majorName.trim().isEmpty()) {
            return majorName;
        }

        // 同时检查英文括号和中文括号
        int englishBracketIndex = majorName.indexOf('(');
        int chineseBracketIndex = majorName.indexOf('（');

        int bracketIndex = -1;
        if (englishBracketIndex > 0 && chineseBracketIndex > 0) {
            // 如果两种括号都存在，选择最早出现的
            bracketIndex = Math.min(englishBracketIndex, chineseBracketIndex);
        } else if (englishBracketIndex > 0) {
            bracketIndex = englishBracketIndex;
        } else if (chineseBracketIndex > 0) {
            bracketIndex = chineseBracketIndex;
        }

        if (bracketIndex > 0) {
            return majorName.substring(0, bracketIndex).trim();
        }

        return majorName;
    }

    /**
     * 查询专业大类统计信息
     * @param province 省份
     * @param year 年份
     * @param educationLevel 教育级别
     * @param type 文理综合类别
     * @return 招生计划列表
     */
    default List<CollegeEnrollmentPlanDO> selectMajorCategoriesWithStats(String province, Integer year,
                                                                        String educationLevel, String type) {
        LambdaQueryWrapperX<CollegeEnrollmentPlanDO> wrapper = new LambdaQueryWrapperX<>();
        wrapper.eqIfPresent(CollegeEnrollmentPlanDO::getProvinceName, province)
               .eqIfPresent(CollegeEnrollmentPlanDO::getYear, year)
               .eqIfPresent(CollegeEnrollmentPlanDO::getType, type)
               .isNotNull(CollegeEnrollmentPlanDO::getClassOne)
               .ne(CollegeEnrollmentPlanDO::getClassOne, "");
        return selectList(wrapper);
    }

    /**
     * 根据专业大类查询招生计划
     * @param categoryName 专业大类名称
     * @param province 省份
     * @param year 年份
     * @param educationLevel 教育级别
     * @param type 文理综合类别
     * @return 招生计划列表
     */
    default List<CollegeEnrollmentPlanDO> selectByMajorCategory(String categoryName, String province,
                                                               Integer year, String educationLevel, String type) {
        LambdaQueryWrapperX<CollegeEnrollmentPlanDO> wrapper = new LambdaQueryWrapperX<>();
        wrapper.eq(CollegeEnrollmentPlanDO::getClassOne, categoryName)
               .eqIfPresent(CollegeEnrollmentPlanDO::getProvinceName, province)
               .eqIfPresent(CollegeEnrollmentPlanDO::getYear, year)
               .eqIfPresent(CollegeEnrollmentPlanDO::getType, type);
        return selectList(wrapper);
    }

    /**
     * 查询学校统计信息
     * @param province 省份
     * @param year 年份
     * @param educationLevel 教育级别
     * @param type 文理综合类别
     * @param schoolNameKeyword 学校名称关键字
     * @param majorCategory 专业大类筛选
     * @return 招生计划列表
     */
    default List<CollegeEnrollmentPlanDO> selectSchoolsWithStats(String province, Integer year,
                                                                String educationLevel, String type,
                                                                String schoolNameKeyword, String majorCategory) {
        LambdaQueryWrapperX<CollegeEnrollmentPlanDO> wrapper = new LambdaQueryWrapperX<>();
        wrapper.eqIfPresent(CollegeEnrollmentPlanDO::getProvinceName, province)
               .eqIfPresent(CollegeEnrollmentPlanDO::getYear, year)
               .eqIfPresent(CollegeEnrollmentPlanDO::getType, type)
               .eqIfPresent(CollegeEnrollmentPlanDO::getClassOne, majorCategory)
               .likeIfPresent(CollegeEnrollmentPlanDO::getSchoolName, schoolNameKeyword)
               .isNotNull(CollegeEnrollmentPlanDO::getSchoolName)
               .ne(CollegeEnrollmentPlanDO::getSchoolName, "");
        return selectList(wrapper);
    }

    /**
     * 根据学校名称查询招生计划
     * @param schoolName 学校名称
     * @param province 省份
     * @param year 年份
     * @param educationLevel 教育级别
     * @param type 文理综合类别
     * @return 招生计划列表
     */
    default List<CollegeEnrollmentPlanDO> selectBySchoolName(String schoolName, String province,
                                                            Integer year, String educationLevel, String type) {
        LambdaQueryWrapperX<CollegeEnrollmentPlanDO> wrapper = new LambdaQueryWrapperX<>();
        wrapper.eq(CollegeEnrollmentPlanDO::getSchoolName, schoolName)
               .eqIfPresent(CollegeEnrollmentPlanDO::getProvinceName, province)
               .eqIfPresent(CollegeEnrollmentPlanDO::getYear, year)
               .eqIfPresent(CollegeEnrollmentPlanDO::getType, type);
        return selectList(wrapper);
    }

    /**
     * 检查选科要求是否满足
     * @param courseSelectionRequirements 选科要求
     * @param userSubjects 用户选科
     * @return 是否满足
     */
    default boolean isSubjectRequirementMet(String courseSelectionRequirements, List<String> userSubjects) {
        // 如果没有选科要求，则认为符合条件
        if (courseSelectionRequirements == null || courseSelectionRequirements.trim().isEmpty()
                || "不限".equals(courseSelectionRequirements) || "无".equals(courseSelectionRequirements)) {
            return true;
        }

        // 简化的选科匹配逻辑（可根据实际需求扩展）
        Set<String> userSubjectsSet = new HashSet<>(userSubjects);

        // 检查是否包含必需的科目
        if (courseSelectionRequirements.contains("物理")) {
            if (!userSubjectsSet.contains("物理")) {
                return false;
            }
        }
        if (courseSelectionRequirements.contains("化学")) {
            if (!userSubjectsSet.contains("化学")) {
                return false;
            }
        }
        if (courseSelectionRequirements.contains("生物")) {
            if (!userSubjectsSet.contains("生物")) {
                return false;
            }
        }
        if (courseSelectionRequirements.contains("历史")) {
            if (!userSubjectsSet.contains("历史")) {
                return false;
            }
        }
        if (courseSelectionRequirements.contains("政治")) {
            if (!userSubjectsSet.contains("政治")) {
                return false;
            }
        }
        if (courseSelectionRequirements.contains("地理")) {
            if (!userSubjectsSet.contains("地理")) {
                return false;
            }
        }

        return true;
    }

    /**
     * 根据唯一键查询招生计划记录
     * @param provinceName 省份名称
     * @param schoolUuid 学校UUID
     * @param collegeMajorName 专业名称
     * @param year 年份
     * @param type 类型
     * @param batchName 批次名称
     * @return 招生计划记录，如果不存在则返回null
     */
    default CollegeEnrollmentPlanDO selectByUniqueKey(String provinceName, String schoolUuid, String collegeMajorName,
                                                     Integer year, String type, String batchName) {
        LambdaQueryWrapperX<CollegeEnrollmentPlanDO> wrapper = new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>();

        // 使用 eq 而不是 eqIfPresent，确保 null 值也能正确匹配
        if (provinceName != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getProvinceName, provinceName);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getProvinceName);
        }

        if (schoolUuid != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getSchoolUuid, schoolUuid);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getSchoolUuid);
        }

        if (collegeMajorName != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getCollegeMajorName, collegeMajorName);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getCollegeMajorName);
        }

        if (year != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getYear, year);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getYear);
        }

        if (type != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getType, type);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getType);
        }

        if (batchName != null) {
            wrapper.eq(CollegeEnrollmentPlanDO::getBatchName, batchName);
        } else {
            wrapper.isNull(CollegeEnrollmentPlanDO::getBatchName);
        }

        List<CollegeEnrollmentPlanDO> results = selectList(wrapper);

        // 如果找到多条记录，记录警告并返回第一条
        if (results.size() > 1) {
            log.warn("发现重复的招生计划记录，期望1条但找到{}条。省份={}, 学校UUID={}, 专业={}, 年份={}, 类型={}, 批次={}",
                    results.size(), provinceName, schoolUuid, collegeMajorName, year, type, batchName);
            // 返回第一条记录，通常是最早创建的
            return results.get(0);
        }

        return results.isEmpty() ? null : results.get(0);
    }



    /**
     * 根据年份统计数据量
     * @param year 年份
     * @return 数据量
     */
    default Long countByYear(Integer year) {
        return selectCount(new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>()
                .eq(CollegeEnrollmentPlanDO::getYear, year));
    }

    /**
     * 根据省份统计数据量
     * @param provinceName 省份名称
     * @return 数据量
     */
    default Long countByProvince(String provinceName) {
        return selectCount(new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>()
                .eq(CollegeEnrollmentPlanDO::getProvinceName, provinceName));
    }

    /**
     * 根据年份和省份统计数据量
     * @param year 年份
     * @param provinceName 省份名称
     * @return 数据量
     */
    default Long countByYearAndProvince(Integer year, String provinceName) {
        return selectCount(new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>()
                .eq(CollegeEnrollmentPlanDO::getYear, year)
                .eq(CollegeEnrollmentPlanDO::getProvinceName, provinceName));
    }

    /**
     * 按学校分组查询招生计划统计数据
     * 优化版本：添加年份过滤和LIMIT来减少内存使用
     * @param schoolName 学校名称（模糊查询）
     * @param collegeMajorName 专业名称（模糊查询）
     * @param provinceName 省份名称
     * @param classOne 专业大类
     * @param classTwo 专业小类
     * @param batchName 录取批次
     * @param type 文理综合类别
     * @param schoolUuid 学校UUID
     * @return 学校分组统计数据
     */
    @Select("<script>" +
            "SELECT " +
            "school_uuid, " +
            "school_name, " +
            "SUM(CASE WHEN enrollment_numbers REGEXP '^[0-9]+$' THEN CAST(enrollment_numbers AS UNSIGNED) ELSE 0 END) as totalEnrollmentNumbers, " +
            "COUNT(DISTINCT college_major_name) as majorCount, " +
            "SUM(CASE WHEN enrollment_numbers REGEXP '^[0-9]+$' THEN CAST(enrollment_numbers AS UNSIGNED) ELSE 0 END) as enrollmentNumbers2024, " +
            "0 as enrollmentNumbers2023, " +
            "0 as enrollmentNumbers2022, " +
            "COUNT(DISTINCT college_major_name) as majorCount2024, " +
            "0 as majorCount2023, " +
            "0 as majorCount2022 " +
            "FROM system_college_enrollment_plan " +
            "WHERE 1=1 " +
            "<if test='year != null and year != 0'>" +
            "AND year = #{year} " +
            "</if>" +
            "<if test='year == null or year == 0'>" +
            "AND year IN (2022, 2023, 2024) " +
            "</if>" +
            "<if test='schoolName != null and schoolName != \"\"'>" +
            "AND school_name LIKE CONCAT('%', #{schoolName}, '%') " +
            "</if>" +
            "<if test='collegeMajorName != null and collegeMajorName != \"\"'>" +
            "AND college_major_name LIKE CONCAT('%', #{collegeMajorName}, '%') " +
            "</if>" +
            "<if test='provinceName != null and provinceName != \"\"'>" +
            "AND province_name = #{provinceName} " +
            "</if>" +
            "<if test='classOne != null and classOne != \"\"'>" +
            "AND class_one = #{classOne} " +
            "</if>" +
            "<if test='classTwo != null and classTwo != \"\"'>" +
            "AND class_two = #{classTwo} " +
            "</if>" +
            "<if test='batchName != null and batchName != \"\"'>" +
            "AND batch_name = #{batchName} " +
            "</if>" +
            "<if test='type != null and type != \"\"'>" +
            "AND type = #{type} " +
            "</if>" +
            "<if test='schoolUuid != null and schoolUuid != \"\"'>" +
            "AND school_uuid = #{schoolUuid} " +
            "</if>" +
            "GROUP BY school_uuid, school_name " +
            "ORDER BY totalEnrollmentNumbers DESC " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    List<Map<String, Object>> selectSchoolGroupStatistics(@Param("schoolName") String schoolName,
                                                          @Param("collegeMajorName") String collegeMajorName,
                                                          @Param("provinceName") String provinceName,
                                                          @Param("classOne") String classOne,
                                                          @Param("classTwo") String classTwo,
                                                          @Param("batchName") String batchName,
                                                          @Param("type") String type,
                                                          @Param("schoolUuid") String schoolUuid,
                                                          @Param("year") Integer year,
                                                          @Param("offset") Integer offset,
                                                          @Param("pageSize") Integer pageSize);

    /**
     * 统计学校分组查询的总数
     */
    @Select("<script>" +
            "SELECT COUNT(DISTINCT school_uuid) " +
            "FROM system_college_enrollment_plan " +
            "WHERE 1=1 " +
            "<if test='year != null and year != 0'>" +
            "AND year = #{year} " +
            "</if>" +
            "<if test='year == null or year == 0'>" +
            "AND year IN (2022, 2023, 2024) " +
            "</if>" +
            "<if test='schoolName != null and schoolName != \"\"'>" +
            "AND school_name LIKE CONCAT('%', #{schoolName}, '%') " +
            "</if>" +
            "<if test='collegeMajorName != null and collegeMajorName != \"\"'>" +
            "AND college_major_name LIKE CONCAT('%', #{collegeMajorName}, '%') " +
            "</if>" +
            "<if test='provinceName != null and provinceName != \"\"'>" +
            "AND province_name = #{provinceName} " +
            "</if>" +
            "<if test='classOne != null and classOne != \"\"'>" +
            "AND class_one = #{classOne} " +
            "</if>" +
            "<if test='classTwo != null and classTwo != \"\"'>" +
            "AND class_two = #{classTwo} " +
            "</if>" +
            "<if test='batchName != null and batchName != \"\"'>" +
            "AND batch_name = #{batchName} " +
            "</if>" +
            "<if test='type != null and type != \"\"'>" +
            "AND type = #{type} " +
            "</if>" +
            "<if test='schoolUuid != null and schoolUuid != \"\"'>" +
            "AND school_uuid = #{schoolUuid} " +
            "</if>" +
            "</script>")
    Long countSchoolGroupStatistics(@Param("schoolName") String schoolName,
                                   @Param("collegeMajorName") String collegeMajorName,
                                   @Param("provinceName") String provinceName,
                                   @Param("classOne") String classOne,
                                   @Param("classTwo") String classTwo,
                                   @Param("batchName") String batchName,
                                   @Param("type") String type,
                                   @Param("schoolUuid") String schoolUuid,
                                   @Param("year") Integer year);

    /**
     * 按专业分组查询招生计划统计数据（简化版，不处理括号）
     * 优化版本：只查询2024年数据，简化查询逻辑
     * @param schoolName 学校名称（模糊查询）
     * @param collegeMajorName 专业名称（模糊查询）
     * @param provinceName 省份名称
     * @param classOne 专业大类
     * @param classTwo 专业小类
     * @param batchName 录取批次
     * @param type 文理综合类别
     * @param schoolUuid 学校UUID
     * @return 专业分组统计数据
     */
    @Select("<script>" +
            "SELECT " +
            "college_major_name as majorName, " +
            "class_one as classOne, " +
            "class_two as classTwo, " +
            "SUM(CASE WHEN enrollment_numbers REGEXP '^[0-9]+$' THEN CAST(enrollment_numbers AS UNSIGNED) ELSE 0 END) as totalEnrollmentNumbers, " +
            "COUNT(DISTINCT school_name) as schoolCount, " +
            "SUM(CASE WHEN enrollment_numbers REGEXP '^[0-9]+$' THEN CAST(enrollment_numbers AS UNSIGNED) ELSE 0 END) as enrollmentNumbers2024, " +
            "0 as enrollmentNumbers2023, " +
            "0 as enrollmentNumbers2022, " +
            "COUNT(DISTINCT school_name) as schoolCount2024, " +
            "0 as schoolCount2023, " +
            "0 as schoolCount2022 " +
            "FROM system_college_enrollment_plan " +
            "WHERE 1=1 " +
            "<if test='year != null and year != 0'>" +
            "AND year = #{year} " +
            "</if>" +
            "<if test='year == null or year == 0'>" +
            "AND year IN (2022, 2023, 2024) " +
            "</if>" +
            "<if test='schoolName != null and schoolName != \"\"'>" +
            "AND school_name LIKE CONCAT('%', #{schoolName}, '%') " +
            "</if>" +
            "<if test='collegeMajorName != null and collegeMajorName != \"\"'>" +
            "AND college_major_name LIKE CONCAT('%', #{collegeMajorName}, '%') " +
            "</if>" +
            "<if test='provinceName != null and provinceName != \"\"'>" +
            "AND province_name = #{provinceName} " +
            "</if>" +
            "<if test='classOne != null and classOne != \"\"'>" +
            "AND class_one = #{classOne} " +
            "</if>" +
            "<if test='classTwo != null and classTwo != \"\"'>" +
            "AND class_two = #{classTwo} " +
            "</if>" +
            "<if test='batchName != null and batchName != \"\"'>" +
            "AND batch_name = #{batchName} " +
            "</if>" +
            "<if test='type != null and type != \"\"'>" +
            "AND type = #{type} " +
            "</if>" +
            "<if test='schoolUuid != null and schoolUuid != \"\"'>" +
            "AND school_uuid = #{schoolUuid} " +
            "</if>" +
            "GROUP BY college_major_name, class_one, class_two " +
            "ORDER BY totalEnrollmentNumbers DESC " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    List<Map<String, Object>> selectMajorGroupStatistics(@Param("schoolName") String schoolName,
                                                         @Param("collegeMajorName") String collegeMajorName,
                                                         @Param("provinceName") String provinceName,
                                                         @Param("classOne") String classOne,
                                                         @Param("classTwo") String classTwo,
                                                         @Param("batchName") String batchName,
                                                         @Param("type") String type,
                                                         @Param("schoolUuid") String schoolUuid,
                                                         @Param("year") Integer year,
                                                         @Param("offset") Integer offset,
                                                         @Param("pageSize") Integer pageSize);

    /**
     * 统计专业分组查询的总数（简化版）
     */
    @Select("<script>" +
            "SELECT COUNT(DISTINCT college_major_name, class_one, class_two) " +
            "FROM system_college_enrollment_plan " +
            "WHERE year = 2024 " +
            "<if test='schoolName != null and schoolName != \"\"'>" +
            "AND school_name LIKE CONCAT('%', #{schoolName}, '%') " +
            "</if>" +
            "<if test='collegeMajorName != null and collegeMajorName != \"\"'>" +
            "AND college_major_name LIKE CONCAT('%', #{collegeMajorName}, '%') " +
            "</if>" +
            "<if test='provinceName != null and provinceName != \"\"'>" +
            "AND province_name = #{provinceName} " +
            "</if>" +
            "<if test='classOne != null and classOne != \"\"'>" +
            "AND class_one = #{classOne} " +
            "</if>" +
            "<if test='classTwo != null and classTwo != \"\"'>" +
            "AND class_two = #{classTwo} " +
            "</if>" +
            "<if test='batchName != null and batchName != \"\"'>" +
            "AND batch_name = #{batchName} " +
            "</if>" +
            "<if test='type != null and type != \"\"'>" +
            "AND type = #{type} " +
            "</if>" +
            "<if test='schoolUuid != null and schoolUuid != \"\"'>" +
            "AND school_uuid = #{schoolUuid} " +
            "</if>" +
            "</script>")
    Long countMajorGroupStatistics(@Param("schoolName") String schoolName,
                                  @Param("collegeMajorName") String collegeMajorName,
                                  @Param("provinceName") String provinceName,
                                  @Param("classOne") String classOne,
                                  @Param("classTwo") String classTwo,
                                  @Param("batchName") String batchName,
                                  @Param("type") String type,
                                  @Param("schoolUuid") String schoolUuid);

    /**
     * 根据学校UUID和专业名称查询多年招生计划数据
     *
     * @param schoolUuid 学校UUID
     * @param majorName 专业名称
     * @param province 省份
     * @param years 年份列表
     * @return 招生计划数据列表
     */
    default List<CollegeEnrollmentPlanDO> selectBySchoolAndMajor(String schoolUuid, String majorName, String province, List<Integer> years) {
        LambdaQueryWrapperX<CollegeEnrollmentPlanDO> wrapper = new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>()
                .eq(CollegeEnrollmentPlanDO::getSchoolUuid, schoolUuid)
                .eq(CollegeEnrollmentPlanDO::getProvinceName, province)
                .in(CollegeEnrollmentPlanDO::getYear, years);

        // 专业名称匹配
        if (majorName != null && !majorName.trim().isEmpty()) {
            wrapper.like(CollegeEnrollmentPlanDO::getCollegeMajorName, majorName);
        }

        return selectList(wrapper.orderByDesc(CollegeEnrollmentPlanDO::getYear));
    }

    /**
     * 根据学校UUID、专业名称和年份查询招生计划数据
     *
     * @param schoolUuid 学校UUID
     * @param majorName 专业名称
     * @param province 省份
     * @param year 年份
     * @return 招生计划数据
     */
    default CollegeEnrollmentPlanDO selectBySchoolMajorAndYear(String schoolUuid, String majorName, String province, Integer year) {
        LambdaQueryWrapperX<CollegeEnrollmentPlanDO> wrapper = new LambdaQueryWrapperX<CollegeEnrollmentPlanDO>()
                .eq(CollegeEnrollmentPlanDO::getSchoolUuid, schoolUuid)
                .eq(CollegeEnrollmentPlanDO::getProvinceName, province)
                .eq(CollegeEnrollmentPlanDO::getYear, year);

        // 专业名称匹配
        if (majorName != null && !majorName.trim().isEmpty()) {
            wrapper.like(CollegeEnrollmentPlanDO::getCollegeMajorName, majorName);
        }

        return selectOne(wrapper);
    }

}
